package com.mybatis.study.test.mapper;

import com.mybatis.study.test.model.UserInfo;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface UserInfoMapper {
    //查询语句
    @Select("select * from userinfo")
    List<UserInfo> selectAll();

    @Select("select * from userinfo")
    @Result(column = "deleteFlg",property = "delete_flg")
    List<UserInfo> selectAll2();

    //传参测试,一个参数的,可以任意命名
    @Select("select * from userinfo where id = ${id}")
    UserInfo selectById(Integer id);

    @Select("select * from userinfo where id = #{id}")
    UserInfo selectById2(Integer id);

    @Select("select * from userinfo where id = #{?}")
    UserInfo selectById3(Integer id);

    @Select("select * from userinfo where id = #{?}")
    UserInfo selectById4(Integer idName);
    //传两个参数
    //SQL语句任意命名会出现MyBatisSystemException
    @Select("select * from userinfo where id = #{?} and username = #{?}")
    UserInfo selectByIdANDName(Integer id,String name);
    //传参任意命名会出现.BindingException: Parameter 'id' not found.
    // Available parameters are [idName, userName, param1, param2]
    @Select("select * from userinfo where id = #{id} and username = #{name}")
    UserInfo selectByIdANDName2(Integer idName,String userName);
    //SQL语句和字段名称保持一致并且和数据库的不同,可以成功
    @Select("select * from userinfo where id = #{id1} and username = #{name1}")
    UserInfo selectByIdANDName3(Integer id1,String name1);
    //SQL语句和字段名称保持一致并且和数据库的相同,可以成功
    @Select("select * from userinfo where id = #{id} and username = #{username}")
    UserInfo selectByIdANDName4(Integer id,String username);
    //使用注解,注解的参数和SQL语句保持一致,传参不一致可以成功
    @Select("select * from userinfo where id = #{id1} and username = #{username1}")
    UserInfo selectByIdANDName5(@Param("id1") Integer id1, @Param("username1") String username1);

    //使用注解,注解的参数和SQL语句不一致,就会导致
    // Parameter 'id1' not found. Available parameters are [id, param1, username, param2]
    @Select("select * from userinfo where id = #{id1} and username = #{username1}")
    UserInfo selectByIdANDName6(@Param("id") Integer id1, @Param("username") String username1);
    //使用注解,注解的参数和SQL语句不一致但是命名为param1,和 param2,出现
    //Parameter 'id' not found. Available parameters are [param1, param2]出现了二义性
    @Select("select * from userinfo where id = #{id} and username = #{username}")
    UserInfo selectByIdANDName7(@Param("param1") Integer id, @Param("param2") String username);

    //增,使用对象传参,返回值为影响的行数
    //SQL语句任意取名导致Caused by: org.apache.ibatis.reflection.ReflectionException:
    // There is no getter for property named 'username1' in 'class com.mybatis.study.Test.model.UserInfo'
    //MyBatis 试图访问 UserInfo 类中的一个名为 username1 的属性，但是在该类中没有找到相应的 getter 方法。
    @Insert("insert into userinfo (username,password,age,gender,phone) " +
            "values (#{username1},#{password1},#{age1},#{gender1},#{phone1})")
    Integer insert(UserInfo userInfo);
    //SQL语句保持一致,参数对象的字段不一致导致了caused by: org.apache.ibatis.reflection.ReflectionException:
    @Insert("insert into userinfo (username,password,age,gender,phone) " +
            "values (#{username},#{password},#{age},#{gender},#{phone})")
    Integer insert2(UserInfo userInfo);
    //SQL语句保持一致,参数对象的字段也保持一致可以成功
    @Insert("insert into userinfo (username,password,age,gender,phone) " +
            "values (#{username},#{password},#{age},#{gender},#{phone})")
    Integer insert3(UserInfo userInfo);
    //SQL语句和数据库不一致,参数对象的字段保持一致导致Caused by: java.sql.SQLSyntaxErrorException
    // Unknown column 'deleteFlag' in 'field list'
    @Insert("insert into userinfo (username,password,age,gender,phone,deleteFlag) " +
            "values (#{username},#{password},#{age},#{gender},#{phone},#{deleteFlag})")
    Integer insert4(UserInfo userInfo);
    //SQL语句和数据库一致,参数对象的字段保持一致,但是我使用@Param注解的话,如果SQL语句如果不和对象对应的话就会出现
    //Parameter 'username' not found. Available parameters are [Userinfo, param1]
    @Insert("insert into userinfo (username,password,age,gender,phone,delete_flag) " +
            "values (#{username},#{password},#{age},#{gender},#{phone},#{deleteFlag})")
    Integer insert5(@Param("userinfo") UserInfo userInfo);

    //SQL语句和数据库不一致,参数对象的字段保持一致,但是我使用@Param注解的话,如果SQL语句和对象对应的话就会出现
    //Parameter 'username' not found. Available parameters are [Userinfo, param1]
    @Insert("insert into userinfo (username,password,age,gender,phone,delete_flag) " +
            "values (#{userinfo.username},#{userinfo.password}," +
            "#{userinfo.age},#{userinfo.gender},#{userinfo.phone},#{userinfo.deleteFlag})")
    Integer insert6(@Param("userinfo") UserInfo userInfo);

    //使用注解获取自增ID
    @Options(useGeneratedKeys = true, keyProperty = "id")
    @Insert("insert into userinfo (username,password,age,gender,phone,delete_flag) " +
            "values (#{userinfo.username},#{userinfo.password}," +
            "#{userinfo.age},#{userinfo.gender},#{userinfo.phone},#{userinfo.deleteFlag})")
    Integer insert7(@Param("userinfo") UserInfo userInfo);
    //重新回到查询的问题,如果数据库的字段和java的对象的字段不一致时该如何解决
    //1.起别名,由于时间问题就不测试了,并且这种方法一般也不用
    //2.使用配置,驼峰方式改成下划线的方式 delete_flg  -> deleteFlg
    //3.使用@Results注解配置数据库字段和java对象属性的映射关系
    @Select("select * from userinfo")
    @Results(id = "BaseMap",value = {
            @Result(column = "delete_flag",property = "deleteFlag"),
            @Result(column = "create_time",property = "createTime"),
            @Result(column = "update_time",property = "updateTime")
    })
    List<UserInfo> selectAll3();
    //4.如果设置过了,可以使用复用映射关系
    @ResultMap("BaseMap")
    @Select("select * from userinfo")
    List<UserInfo> selectAll4();

    //删,注意在企业中一般不使用删除
    //如果要删除1.逻辑删除,设置一个类似delete_flag标记,如果要删就设为1,这样子,逻辑上的删除,并没有把数据删去
    //2.物理删除加副本,如果实在要删数据的话就加一个备份
    //这个就不演示 企业中的用法了,直接练习物理删除
    @Delete("delete from userinfo where id = #{id}")
    Integer deleteById(Integer id);

    //改,这里就不测试不同参数的形式了,直接测试
    @Update("update userinfo set gender = 2 where id = #{id}")
    Integer updateById(Integer id);
}
